SELECT TOP 100 PERCENT 
-- Abajo estan los nombres de los campos
NOMBRE_DF,BHID_DF,SAMPLEID
,CDA_LOCAL_X AS TOP_X
,CDA_LOCAL_Y AS TOP_Y
,CDA_LOCAL_Z AS TOP_Z
,CDA_LOCAL_X AS MID_X
,CDA_LOCAL_Y AS MID_Y
,CASE when CDA_LOCAL_Z is not null and SAMPTO is not null and SAMPFROM is not null  then CDA_LOCAL_Z-(SAMPTO-SAMPFROM)/2 else null end AS MID_Z
,CDA_LOCAL_X AS BOT_X
,CDA_LOCAL_Y AS BOT_Y
,CASE when CDA_LOCAL_Z is not null and SAMPTO is not null and SAMPFROM is not null  then CDA_LOCAL_Z-SAMPTO else null end AS BOT_Z
,SAMPFROM,SAMPTO,CUAS_ASCDA_PCT,
CUCN_ASCDA_PCT,CUS_AAS_PCT,TCU_ASCDA_PCT,MINZONE_D,RCU_PCT_D,
ROCA_D,AL_ALT1,AL_ALT2,LT_ROCK,MN_ALT1,MN_ALT2,MN_ALT3,MN_ALT4,
MN_ALT5,MN_ALT6,MN_ALT7,MN_ALT8,MN_OXCU1,MN_OXCU2,MN_OXCU3,
MN_OXCU4,MN_OXCU5,MN_OXCU6,MN_OXCU7,MN_OXCU8,MN_OXFE1,MN_OXFE2,
MN_OXFE3,MN_OXFE4,MN_OXFE5,MN_OXFE6,MN_OXFE7,MN_OXFE8,MN_SUL1,
MN_SUL2,MN_SUL3,MN_SUL4,MN_SUL5,MN_SUL6,MN_SUL7,
MN_SUL8,MN_ZONE,MN_ZONE_2
-- Arriba estan los nombres de los campos

FROM ( SELECT [SAMPLEID], [SAMPFROM],
		 substring(HOLEID,1,6) NOMBRE_DF,
		 substring(HOLEID,7,6) BHID_DF,
		 ( [dbo].[QFN_MAJ_GEOCODE]([HOLEID],[PROJECTCODE], [SAMPFROM],[SAMPTO], 1,'MN_ZONE', 1) )  AS [MINZONE_D],
		 ( cast([CUAS_ASCDA_PCT] as float)+cast([CUCN_ASCDA_PCT] as float) )  AS [RCU_PCT_D], 
		 ( [dbo].[QFN_MAJ_GEOCODE]([HOLEID], [PROJECTCODE], [SAMPFROM], [SAMPTO], 1, 'LT_ROCK', 1) )  AS [ROCA_D],
		[SAMPTO], CAST([CUAS_ASCDA_PCT] AS FLOAT) AS [CUAS_ASCDA_PCT], 
		CAST([CUCN_ASCDA_PCT] AS FLOAT) AS [CUCN_ASCDA_PCT], 
		CAST([CUS_AAS_PCT] AS FLOAT) AS [CUS_AAS_PCT], 
		CAST([TCU_ASCDA_PCT] AS FLOAT) AS [TCU_ASCDA_PCT], 
		[AL_ALT1], [AL_ALT2], [LT_ROCK], 
		[MN_ALT1], [MN_ALT2], [MN_ALT3], 
		[MN_ALT4], [MN_ALT5], [MN_ALT6], 
		[MN_ALT7], [MN_ALT8], [MN_OXCU1], 
		[MN_OXCU2], [MN_OXCU3], [MN_OXCU4], 
		[MN_OXCU5], [MN_OXCU6], [MN_OXCU7], 
		[MN_OXCU8], [MN_OXFE1], [MN_OXFE2], 
		[MN_OXFE3], [MN_OXFE4], [MN_OXFE5], 
		[MN_OXFE6], [MN_OXFE7], [MN_OXFE8], 
		[MN_SUL1], [MN_SUL2], [MN_SUL3], 
		[MN_SUL4], [MN_SUL5], [MN_SUL6], 
		[MN_SUL7], [MN_SUL8], [MN_ZONE], [MN_ZONE_2], [HOLEID], [PROJECTCODE],
		[CDA_LOCAL_X],[CDA_LOCAL_Y],[CDA_LOCAL_Z] 
	FROM (SELECT 
		[QV_SAMPLE_HOLELOCATION].[SAMPLEID]
		,[QV_SAMPLE_HOLELOCATION].[SAMPFROM]
		,[QV_SAMPLE_HOLELOCATION].[SAMPTO]
		,[CORPSAMPLEASSAY_N].[CUAS_ASCDA_PCT]
		,[CORPSAMPLEASSAY_N].[CUCN_ASCDA_PCT]
		,[CORPSAMPLEASSAY_N].[CUS_AAS_PCT]
		,[CORPSAMPLEASSAY_N].[TCU_ASCDA_PCT]
		,[POINTGEOLOGY].[AL_ALT1]
		,[POINTGEOLOGY].[AL_ALT2]
		,[POINTGEOLOGY].[LT_ROCK]
		,[POINTGEOLOGY].[MN_ALT1]
		,[POINTGEOLOGY].[MN_ALT2]
		,[POINTGEOLOGY].[MN_ALT3]
		,[POINTGEOLOGY].[MN_ALT4]
		,[POINTGEOLOGY].[MN_ALT5]
		,[POINTGEOLOGY].[MN_ALT6]
		,[POINTGEOLOGY].[MN_ALT7]
		,[POINTGEOLOGY].[MN_ALT8]
		,[POINTGEOLOGY].[MN_OXCU1]
		,[POINTGEOLOGY].[MN_OXCU2]
		,[POINTGEOLOGY].[MN_OXCU3]
		,[POINTGEOLOGY].[MN_OXCU4]
		,[POINTGEOLOGY].[MN_OXCU5]
		,[POINTGEOLOGY].[MN_OXCU6]
		,[POINTGEOLOGY].[MN_OXCU7]
		,[POINTGEOLOGY].[MN_OXCU8]
		,[POINTGEOLOGY].[MN_OXFE1]
		,[POINTGEOLOGY].[MN_OXFE2]
		,[POINTGEOLOGY].[MN_OXFE3]
		,[POINTGEOLOGY].[MN_OXFE4]
		,[POINTGEOLOGY].[MN_OXFE5]
		,[POINTGEOLOGY].[MN_OXFE6]
		,[POINTGEOLOGY].[MN_OXFE7]
		,[POINTGEOLOGY].[MN_OXFE8]
		,[POINTGEOLOGY].[MN_SUL1]
		,[POINTGEOLOGY].[MN_SUL2]
		,[POINTGEOLOGY].[MN_SUL3]
		,[POINTGEOLOGY].[MN_SUL4]
		,[POINTGEOLOGY].[MN_SUL5]
		,[POINTGEOLOGY].[MN_SUL6]
		,[POINTGEOLOGY].[MN_SUL7]
		,[POINTGEOLOGY].[MN_SUL8]
		,[POINTGEOLOGY].[MN_ZONE]
		,[POINTGEOLOGY].[MN_ZONE_2]
		,[QV_SAMPLE_HOLELOCATION].[HOLEID]
		,[QV_SAMPLE_HOLELOCATION].[PROJECTCODE]
		,[QV_HOLECOORD_SAMPLEID].[CDA_LOCAL_X]
		,[QV_HOLECOORD_SAMPLEID].[CDA_LOCAL_Y] 
		,[QV_HOLECOORD_SAMPLEID].[CDA_LOCAL_Z] 
FROM [QV_SAMPLE_HOLELOCATION]
    LEFT JOIN 
      (SELECT SAMPLEID
            ,min(CASE when T4.NAME = 'CUAS_ASCDA_PCT' then T4.VALUE ELSE NULL END) as [CUAS_ASCDA_PCT]
            ,min(CASE when T4.NAME = 'CUCN_ASCDA_PCT' then T4.VALUE ELSE NULL END) as [CUCN_ASCDA_PCT]
            ,min(CASE when T4.NAME = 'CUS_AAS_PCT' then T4.VALUE ELSE NULL END) as [CUS_AAS_PCT]
            ,min(CASE when T4.NAME = 'TCU_ASCDA_PCT' then T4.VALUE ELSE NULL END) as [TCU_ASCDA_PCT]
       FROM    
         (SELECT T1.SAMPLEID
                 ,
T1.NAME
                 ,CASE WHEN T1.VALUE IS NOT NULL AND T1.DSC IS NULL THEN T1.VALUE 
                       WHEN T1.VALUE IS NOT NULL AND T1.DSC IS NOT NULL AND T2.EXPRULE1 IS NULL THEN T1.VALUE*-1
                       ELSE T2.EXPRULE1
                       END as VALUE
          FROM CORPSAMPLEASSAY T1
          LEFT JOIN CORPASSAYTRANS T2
          ON ((T1.NAME=T2.NAME AND T1.VALUE=T2.VALUE AND T1.DSC=T2.DSC AND T1.VALUE IS NOT NULL)
               OR (T1.NAME=T2.NAME AND T1.DSC=T2.DSC AND T1.VALUE IS NULL AND T2.VALUE IS NULL))
          WHERE T1.PRIORITY=1
         ) T4
       GROUP BY SAMPLEID
      ) [CORPSAMPLEASSAY_N]
    ON [QV_SAMPLE_HOLELOCATION].SAMPLEID = [CORPSAMPLEASSAY_N].SAMPLEID 
       LEFT JOIN
         (SELECT
[POINTGEOLOGY].[SAMPLEID]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'AL_ALT1' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [AL_ALT1]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'AL_ALT2' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [AL_ALT2]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'LT_ROCK' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [LT_ROCK]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_ALT1' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_ALT1]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_ALT2' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_ALT2]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_ALT3' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_ALT3]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_ALT4' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_ALT4]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_ALT5' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_ALT5]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_ALT6' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_ALT6]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_ALT7' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_ALT7]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_ALT8' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_ALT8]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXCU1' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXCU1]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXCU2' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXCU2]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXCU3' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXCU3]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXCU4' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXCU4]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXCU5' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXCU5]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXCU6' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXCU6]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXCU7' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXCU7]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXCU8' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXCU8]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXFE1' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXFE1]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXFE2' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXFE2]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXFE3' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXFE3]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXFE4' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXFE4]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXFE5' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXFE5]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXFE6' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXFE6]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXFE7' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXFE7]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_OXFE8' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_OXFE8]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_SUL1' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_SUL1]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_SUL2' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_SUL2]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_SUL3' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_SUL3]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_SUL4' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_SUL4]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_SUL5' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_SUL5]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_SUL6' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_SUL6]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_SUL7' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_SUL7]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_SUL8' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_SUL8]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_ZONE' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_ZONE]
             ,min(CASE when [POINTGEOLOGY].[NAME] = 'MN_ZONE_2' then [POINTGEOLOGY].[VALUE] ELSE NULL END) as [MN_ZONE_2]
         FROM [POINTGEOLOGY]
         GROUP BY 
[POINTGEOLOGY].[SAMPLEID]
         ) [POINTGEOLOGY]
       ON
[QV_SAMPLE_HOLELOCATION].[SAMPLEID] = [POINTGEOLOGY].[SAMPLEID]
		LEFT JOIN
            (SELECT HOLEID,PROJECTCODE
            ,min(CASE when [QV_HOLECOORD_SAMPLEID].[COORDINATESET] = 'CDA_LOCAL' then [QV_HOLECOORD_SAMPLEID].[X] ELSE NULL END) as [CDA_LOCAL_X]
            ,min(CASE when [QV_HOLECOORD_SAMPLEID].[COORDINATESET] = 'CDA_LOCAL' then [QV_HOLECOORD_SAMPLEID].[Y] ELSE NULL END) as [CDA_LOCAL_Y]
            ,min(CASE when [QV_HOLECOORD_SAMPLEID].[COORDINATESET] = 'CDA_LOCAL' then [QV_HOLECOORD_SAMPLEID].[Z] ELSE NULL END) as [CDA_LOCAL_Z]
       FROM HOLECOORD [QV_HOLECOORD_SAMPLEID]
       GROUP BY PROJECTCODE,HOLEID) [QV_HOLECOORD_SAMPLEID]
     ON [QV_SAMPLE_HOLELOCATION].HOLEID = [QV_HOLECOORD_SAMPLEID].HOLEID
     AND [QV_SAMPLE_HOLELOCATION].PROJECTCODE = [QV_HOLECOORD_SAMPLEID].PROJECTCODE
) [TMPVIEW9] ) A

where HOLEID like '121314%'
